{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# LOAD data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a,b,c,d,message\r\n",
      "1,2,3,4,hello\r\n",
      "5,6,7,8,world\r\n",
      "9,10,11,12,foo"
     ]
    }
   ],
   "source": [
    "!cat '/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex1.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a   b   c   d message\n",
       "0  1   2   3   4   hello\n",
       "1  5   6   7   8   world\n",
       "2  9  10  11  12     foo"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>hello</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   1   2   3   4  hello\n",
       "0  5   6   7   8  world\n",
       "1  9  10  11  12    foo"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex2.csv',header = None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   0   1   2   3      4\n",
       "0  1   2   3   4  hello\n",
       "1  5   6   7   8  world\n",
       "2  9  10  11  12    foo"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex2.csv',names = ['a','b','c','d','message'],index_col=['message'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>message</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>hello</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>world</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         a   b   c   d\n",
       "message               \n",
       "hello    1   2   3   4\n",
       "world    5   6   7   8\n",
       "foo      9  10  11  12"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/csv_mindex.csv',index_col=['key1','key2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>value1</th>\n",
       "      <th>value2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">one</th>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">two</th>\n",
       "      <th>a</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           value1  value2\n",
       "key1 key2                \n",
       "one  a          1       2\n",
       "     b          3       4\n",
       "     c          5       6\n",
       "     d          7       8\n",
       "two  a          9      10\n",
       "     b         11      12\n",
       "     c         13      14\n",
       "     d         15      16"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['            A         B         C\\n',\n",
       " 'aaa -0.264438 -1.026059 -0.619500\\n',\n",
       " 'bbb  0.927272  0.302904 -0.032399\\n',\n",
       " 'ccc -0.264273 -0.386314 -0.217601\\n',\n",
       " 'ddd -0.871858 -0.348382  1.100491']"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(open('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex3.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_table('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex3.txt',sep='\\s+')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>aaa</th>\n",
       "      <td>-0.264438</td>\n",
       "      <td>-1.026059</td>\n",
       "      <td>-0.619500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bbb</th>\n",
       "      <td>0.927272</td>\n",
       "      <td>0.302904</td>\n",
       "      <td>-0.032399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ccc</th>\n",
       "      <td>-0.264273</td>\n",
       "      <td>-0.386314</td>\n",
       "      <td>-0.217601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ddd</th>\n",
       "      <td>-0.871858</td>\n",
       "      <td>-0.348382</td>\n",
       "      <td>1.100491</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            A         B         C\n",
       "aaa -0.264438 -1.026059 -0.619500\n",
       "bbb  0.927272  0.302904 -0.032399\n",
       "ccc -0.264273 -0.386314 -0.217601\n",
       "ddd -0.871858 -0.348382  1.100491"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_table('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex3.txt',sep='\\s+')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>aaa</th>\n",
       "      <td>-0.264438</td>\n",
       "      <td>-1.026059</td>\n",
       "      <td>-0.619500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bbb</th>\n",
       "      <td>0.927272</td>\n",
       "      <td>0.302904</td>\n",
       "      <td>-0.032399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ccc</th>\n",
       "      <td>-0.264273</td>\n",
       "      <td>-0.386314</td>\n",
       "      <td>-0.217601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ddd</th>\n",
       "      <td>-0.871858</td>\n",
       "      <td>-0.348382</td>\n",
       "      <td>1.100491</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            A         B         C\n",
       "aaa -0.264438 -1.026059 -0.619500\n",
       "bbb  0.927272  0.302904 -0.032399\n",
       "ccc -0.264273 -0.386314 -0.217601\n",
       "ddd -0.871858 -0.348382  1.100491"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex5.csv',na_values={'message':['foo','NA'],'something':['two']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>three</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  something  a   b     c   d message\n",
       "0       one  1   2   3.0   4     NaN\n",
       "1       NaN  5   6   NaN   8   world\n",
       "2     three  9  10  11.0  12     NaN"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex6.csv',chunksize=1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0      L\n",
      "1      B\n",
      "2      G\n",
      "3      R\n",
      "4      Q\n",
      "5      Q\n",
      "6      U\n",
      "7      K\n",
      "8      S\n",
      "9      G\n",
      "10     8\n",
      "11     R\n",
      "12     1\n",
      "13     P\n",
      "14     J\n",
      "15     E\n",
      "16     B\n",
      "17     A\n",
      "18     F\n",
      "19     H\n",
      "20     W\n",
      "21     C\n",
      "22     C\n",
      "23     V\n",
      "24     I\n",
      "25     S\n",
      "26     L\n",
      "27     6\n",
      "28     J\n",
      "29     Y\n",
      "      ..\n",
      "970    P\n",
      "971    X\n",
      "972    O\n",
      "973    R\n",
      "974    W\n",
      "975    O\n",
      "976    S\n",
      "977    R\n",
      "978    G\n",
      "979    X\n",
      "980    O\n",
      "981    K\n",
      "982    M\n",
      "983    Q\n",
      "984    Q\n",
      "985    R\n",
      "986    N\n",
      "987    C\n",
      "988    X\n",
      "989    I\n",
      "990    V\n",
      "991    4\n",
      "992    Q\n",
      "993    O\n",
      "994    W\n",
      "995    M\n",
      "996    H\n",
      "997    D\n",
      "998    W\n",
      "999    K\n",
      "Name: key, dtype: object\n",
      "Begine count\n",
      "S    48\n",
      "O    44\n",
      "F    40\n",
      "J    39\n",
      "Q    39\n",
      "H    39\n",
      "G    38\n",
      "R    38\n",
      "I    37\n",
      "X    37\n",
      "V    35\n",
      "U    33\n",
      "D    32\n",
      "E    32\n",
      "L    31\n",
      "K    31\n",
      "W    31\n",
      "A    30\n",
      "M    29\n",
      "Y    28\n",
      "C    27\n",
      "T    27\n",
      "N    27\n",
      "Z    26\n",
      "B    25\n",
      "P    25\n",
      "6    17\n",
      "7    17\n",
      "4    17\n",
      "3    15\n",
      "8    13\n",
      "1    13\n",
      "9    11\n",
      "2    11\n",
      "5     9\n",
      "0     9\n",
      "Name: key, dtype: int64\n",
      "1000    T\n",
      "1001    J\n",
      "1002    R\n",
      "1003    S\n",
      "1004    B\n",
      "1005    Q\n",
      "1006    Z\n",
      "1007    X\n",
      "1008    R\n",
      "1009    4\n",
      "1010    5\n",
      "1011    A\n",
      "1012    J\n",
      "1013    L\n",
      "1014    N\n",
      "1015    I\n",
      "1016    6\n",
      "1017    T\n",
      "1018    R\n",
      "1019    4\n",
      "1020    Z\n",
      "1021    K\n",
      "1022    B\n",
      "1023    O\n",
      "1024    G\n",
      "1025    F\n",
      "1026    I\n",
      "1027    K\n",
      "1028    L\n",
      "1029    O\n",
      "       ..\n",
      "1970    V\n",
      "1971    J\n",
      "1972    Q\n",
      "1973    R\n",
      "1974    T\n",
      "1975    R\n",
      "1976    3\n",
      "1977    O\n",
      "1978    O\n",
      "1979    8\n",
      "1980    A\n",
      "1981    7\n",
      "1982    V\n",
      "1983    S\n",
      "1984    W\n",
      "1985    O\n",
      "1986    M\n",
      "1987    S\n",
      "1988    D\n",
      "1989    P\n",
      "1990    B\n",
      "1991    D\n",
      "1992    F\n",
      "1993    O\n",
      "1994    Q\n",
      "1995    L\n",
      "1996    J\n",
      "1997    V\n",
      "1998    W\n",
      "1999    D\n",
      "Name: key, dtype: object\n",
      "Begine count\n",
      "O    48\n",
      "L    44\n",
      "X    40\n",
      "I    39\n",
      "R    38\n",
      "F    37\n",
      "Q    35\n",
      "D    34\n",
      "V    33\n",
      "K    33\n",
      "E    32\n",
      "J    31\n",
      "A    31\n",
      "T    31\n",
      "H    31\n",
      "Z    30\n",
      "U    30\n",
      "N    30\n",
      "M    28\n",
      "S    27\n",
      "Y    27\n",
      "P    26\n",
      "G    26\n",
      "W    25\n",
      "B    24\n",
      "C    23\n",
      "5    20\n",
      "0    19\n",
      "8    19\n",
      "9    19\n",
      "4    18\n",
      "3    16\n",
      "1    16\n",
      "6    14\n",
      "2    14\n",
      "7    12\n",
      "Name: key, dtype: int64\n",
      "2000    1\n",
      "2001    H\n",
      "2002    F\n",
      "2003    L\n",
      "2004    E\n",
      "2005    3\n",
      "2006    G\n",
      "2007    X\n",
      "2008    D\n",
      "2009    Q\n",
      "2010    N\n",
      "2011    W\n",
      "2012    T\n",
      "2013    M\n",
      "2014    M\n",
      "2015    X\n",
      "2016    Z\n",
      "2017    T\n",
      "2018    E\n",
      "2019    H\n",
      "2020    U\n",
      "2021    Q\n",
      "2022    I\n",
      "2023    1\n",
      "2024    Y\n",
      "2025    9\n",
      "2026    E\n",
      "2027    C\n",
      "2028    6\n",
      "2029    K\n",
      "       ..\n",
      "2970    4\n",
      "2971    L\n",
      "2972    M\n",
      "2973    X\n",
      "2974    9\n",
      "2975    F\n",
      "2976    C\n",
      "2977    K\n",
      "2978    S\n",
      "2979    X\n",
      "2980    H\n",
      "2981    K\n",
      "2982    8\n",
      "2983    G\n",
      "2984    3\n",
      "2985    Y\n",
      "2986    K\n",
      "2987    P\n",
      "2988    U\n",
      "2989    Y\n",
      "2990    U\n",
      "2991    V\n",
      "2992    H\n",
      "2993    A\n",
      "2994    0\n",
      "2995    H\n",
      "2996    U\n",
      "2997    A\n",
      "2998    Y\n",
      "2999    F\n",
      "Name: key, dtype: object\n",
      "Begine count\n",
      "O    40\n",
      "A    40\n",
      "X    39\n",
      "E    39\n",
      "M    38\n",
      "H    37\n",
      "T    36\n",
      "G    36\n",
      "L    34\n",
      "K    34\n",
      "U    33\n",
      "F    32\n",
      "B    32\n",
      "N    31\n",
      "V    31\n",
      "P    30\n",
      "S    30\n",
      "Z    29\n",
      "J    29\n",
      "W    29\n",
      "Q    28\n",
      "R    28\n",
      "D    28\n",
      "Y    27\n",
      "C    27\n",
      "I    25\n",
      "6    20\n",
      "9    19\n",
      "0    19\n",
      "7    18\n",
      "3    15\n",
      "8    14\n",
      "4    14\n",
      "5    14\n",
      "2    14\n",
      "1    11\n",
      "Name: key, dtype: int64\n",
      "3000    H\n",
      "3001    Y\n",
      "3002    0\n",
      "3003    Z\n",
      "3004    U\n",
      "3005    B\n",
      "3006    D\n",
      "3007    J\n",
      "3008    7\n",
      "3009    2\n",
      "3010    R\n",
      "3011    X\n",
      "3012    A\n",
      "3013    0\n",
      "3014    U\n",
      "3015    B\n",
      "3016    E\n",
      "3017    W\n",
      "3018    T\n",
      "3019    U\n",
      "3020    P\n",
      "3021    P\n",
      "3022    D\n",
      "3023    T\n",
      "3024    1\n",
      "3025    N\n",
      "3026    A\n",
      "3027    J\n",
      "3028    A\n",
      "3029    E\n",
      "       ..\n",
      "3970    H\n",
      "3971    O\n",
      "3972    A\n",
      "3973    X\n",
      "3974    Y\n",
      "3975    F\n",
      "3976    E\n",
      "3977    D\n",
      "3978    S\n",
      "3979    U\n",
      "3980    5\n",
      "3981    B\n",
      "3982    T\n",
      "3983    C\n",
      "3984    H\n",
      "3985    7\n",
      "3986    J\n",
      "3987    N\n",
      "3988    U\n",
      "3989    2\n",
      "3990    5\n",
      "3991    I\n",
      "3992    6\n",
      "3993    S\n",
      "3994    V\n",
      "3995    W\n",
      "3996    E\n",
      "3997    Q\n",
      "3998    A\n",
      "3999    M\n",
      "Name: key, dtype: object\n",
      "Begine count\n",
      "X    43\n",
      "J    41\n",
      "V    38\n",
      "D    38\n",
      "Q    38\n",
      "E    37\n",
      "C    37\n",
      "N    36\n",
      "B    35\n",
      "P    35\n",
      "F    35\n",
      "L    33\n",
      "S    32\n",
      "K    32\n",
      "R    31\n",
      "A    31\n",
      "M    30\n",
      "H    30\n",
      "O    29\n",
      "W    28\n",
      "Y    28\n",
      "I    28\n",
      "G    27\n",
      "U    26\n",
      "Z    24\n",
      "T    23\n",
      "5    21\n",
      "6    18\n",
      "3    17\n",
      "2    16\n",
      "1    16\n",
      "9    15\n",
      "0    15\n",
      "8    13\n",
      "7    12\n",
      "4    12\n",
      "Name: key, dtype: int64\n",
      "4000    H\n",
      "4001    Z\n",
      "4002    2\n",
      "4003    B\n",
      "4004    1\n",
      "4005    R\n",
      "4006    R\n",
      "4007    L\n",
      "4008    8\n",
      "4009    M\n",
      "4010    K\n",
      "4011    8\n",
      "4012    U\n",
      "4013    5\n",
      "4014    M\n",
      "4015    0\n",
      "4016    B\n",
      "4017    K\n",
      "4018    Y\n",
      "4019    N\n",
      "4020    K\n",
      "4021    E\n",
      "4022    A\n",
      "4023    A\n",
      "4024    8\n",
      "4025    I\n",
      "4026    I\n",
      "4027    L\n",
      "4028    U\n",
      "4029    L\n",
      "       ..\n",
      "4970    K\n",
      "4971    N\n",
      "4972    F\n",
      "4973    H\n",
      "4974    G\n",
      "4975    3\n",
      "4976    5\n",
      "4977    K\n",
      "4978    Z\n",
      "4979    4\n",
      "4980    L\n",
      "4981    C\n",
      "4982    M\n",
      "4983    P\n",
      "4984    K\n",
      "4985    7\n",
      "4986    I\n",
      "4987    U\n",
      "4988    K\n",
      "4989    O\n",
      "4990    4\n",
      "4991    O\n",
      "4992    0\n",
      "4993    S\n",
      "4994    H\n",
      "4995    X\n",
      "4996    M\n",
      "4997    5\n",
      "4998    T\n",
      "4999    U\n",
      "Name: key, dtype: object\n",
      "Begine count\n",
      "E    54\n",
      "Q    42\n",
      "L    40\n",
      "H    39\n",
      "K    38\n",
      "P    38\n",
      "M    36\n",
      "Y    35\n",
      "X    35\n",
      "D    34\n",
      "W    33\n",
      "U    33\n",
      "N    33\n",
      "Z    31\n",
      "A    31\n",
      "F    31\n",
      "I    31\n",
      "T    31\n",
      "G    30\n",
      "B    27\n",
      "C    26\n",
      "V    26\n",
      "O    26\n",
      "J    25\n",
      "S    24\n",
      "2    20\n",
      "R    18\n",
      "0    18\n",
      "3    18\n",
      "4    18\n",
      "6    17\n",
      "5    17\n",
      "8    16\n",
      "7    13\n",
      "9     8\n",
      "1     8\n",
      "Name: key, dtype: int64\n",
      "5000    1\n",
      "5001    Z\n",
      "5002    3\n",
      "5003    H\n",
      "5004    B\n",
      "5005    L\n",
      "5006    O\n",
      "5007    8\n",
      "5008    W\n",
      "5009    G\n",
      "5010    Y\n",
      "5011    S\n",
      "5012    E\n",
      "5013    R\n",
      "5014    B\n",
      "5015    L\n",
      "5016    Z\n",
      "5017    L\n",
      "5018    F\n",
      "5019    8\n",
      "5020    4\n",
      "5021    K\n",
      "5022    Y\n",
      "5023    R\n",
      "5024    T\n",
      "5025    S\n",
      "5026    O\n",
      "5027    V\n",
      "5028    P\n",
      "5029    B\n",
      "       ..\n",
      "5970    Y\n",
      "5971    E\n",
      "5972    Q\n",
      "5973    P\n",
      "5974    X\n",
      "5975    M\n",
      "5976    W\n",
      "5977    O\n",
      "5978    T\n",
      "5979    L\n",
      "5980    A\n",
      "5981    O\n",
      "5982    P\n",
      "5983    E\n",
      "5984    N\n",
      "5985    O\n",
      "5986    G\n",
      "5987    I\n",
      "5988    4\n",
      "5989    I\n",
      "5990    L\n",
      "5991    H\n",
      "5992    X\n",
      "5993    G\n",
      "5994    R\n",
      "5995    1\n",
      "5996    Y\n",
      "5997    F\n",
      "5998    0\n",
      "5999    3\n",
      "Name: key, dtype: object\n",
      "Begine count\n",
      "Y    42\n",
      "K    41\n",
      "F    41\n",
      "X    38\n",
      "V    37\n",
      "W    35\n",
      "E    35\n",
      "L    35\n",
      "J    34\n",
      "R    33\n",
      "M    33\n",
      "I    32\n",
      "N    32\n",
      "P    32\n",
      "U    32\n",
      "D    31\n",
      "S    31\n",
      "B    30\n",
      "H    28\n",
      "G    28\n",
      "Q    27\n",
      "O    26\n",
      "C    25\n",
      "A    25\n",
      "1    25\n",
      "T    24\n",
      "Z    23\n",
      "5    22\n",
      "9    20\n",
      "7    20\n",
      "8    17\n",
      "6    17\n",
      "3    16\n",
      "4    13\n",
      "2    12\n",
      "0     8\n",
      "Name: key, dtype: int64\n",
      "6000    I\n",
      "6001    X\n",
      "6002    A\n",
      "6003    C\n",
      "6004    S\n",
      "6005    T\n",
      "6006    M\n",
      "6007    4\n",
      "6008    N\n",
      "6009    H\n",
      "6010    V\n",
      "6011    I\n",
      "6012    7\n",
      "6013    X\n",
      "6014    K\n",
      "6015    8\n",
      "6016    Z\n",
      "6017    F\n",
      "6018    N\n",
      "6019    V\n",
      "6020    J\n",
      "6021    I\n",
      "6022    U\n",
      "6023    R\n",
      "6024    D\n",
      "6025    F\n",
      "6026    F\n",
      "6027    2\n",
      "6028    B\n",
      "6029    Z\n",
      "       ..\n",
      "6970    G\n",
      "6971    M\n",
      "6972    Y\n",
      "6973    E\n",
      "6974    U\n",
      "6975    L\n",
      "6976    N\n",
      "6977    J\n",
      "6978    P\n",
      "6979    S\n",
      "6980    O\n",
      "6981    Z\n",
      "6982    V\n",
      "6983    O\n",
      "6984    G\n",
      "6985    N\n",
      "6986    E\n",
      "6987    I\n",
      "6988    4\n",
      "6989    I\n",
      "6990    T\n",
      "6991    7\n",
      "6992    2\n",
      "6993    Z\n",
      "6994    F\n",
      "6995    P\n",
      "6996    9\n",
      "6997    L\n",
      "6998    5\n",
      "6999    O\n",
      "Name: key, dtype: object\n",
      "Begine count\n",
      "C    41\n",
      "L    41\n",
      "Z    39\n",
      "W    39\n",
      "X    38\n",
      "O    37\n",
      "I    36\n",
      "T    36\n",
      "R    35\n",
      "H    34\n",
      "J    33\n",
      "N    32\n",
      "G    31\n",
      "F    31\n",
      "D    31\n",
      "V    30\n",
      "P    30\n",
      "E    30\n",
      "B    30\n",
      "M    29\n",
      "U    29\n",
      "Q    28\n",
      "8    25\n",
      "Y    24\n",
      "A    23\n",
      "K    22\n",
      "7    22\n",
      "S    21\n",
      "9    20\n",
      "4    18\n",
      "0    18\n",
      "2    17\n",
      "1    16\n",
      "5    15\n",
      "3    11\n",
      "6     8\n",
      "Name: key, dtype: int64\n",
      "7000    1\n",
      "7001    I\n",
      "7002    H\n",
      "7003    P\n",
      "7004    D\n",
      "7005    J\n",
      "7006    D\n",
      "7007    0\n",
      "7008    A\n",
      "7009    A\n",
      "7010    I\n",
      "7011    L\n",
      "7012    C\n",
      "7013    7\n",
      "7014    V\n",
      "7015    E\n",
      "7016    P\n",
      "7017    G\n",
      "7018    E\n",
      "7019    W\n",
      "7020    A\n",
      "7021    6\n",
      "7022    5\n",
      "7023    T\n",
      "7024    J\n",
      "7025    K\n",
      "7026    8\n",
      "7027    E\n",
      "7028    J\n",
      "7029    D\n",
      "       ..\n",
      "7970    E\n",
      "7971    D\n",
      "7972    L\n",
      "7973    A\n",
      "7974    G\n",
      "7975    X\n",
      "7976    Q\n",
      "7977    W\n",
      "7978    C\n",
      "7979    G\n",
      "7980    T\n",
      "7981    7\n",
      "7982    R\n",
      "7983    K\n",
      "7984    R\n",
      "7985    D\n",
      "7986    R\n",
      "7987    R\n",
      "7988    8\n",
      "7989    T\n",
      "7990    U\n",
      "7991    7\n",
      "7992    W\n",
      "7993    8\n",
      "7994    A\n",
      "7995    A\n",
      "7996    6\n",
      "7997    R\n",
      "7998    R\n",
      "7999    2\n",
      "Name: key, dtype: object\n",
      "Begine count\n",
      "J    42\n",
      "M    40\n",
      "E    39\n",
      "D    39\n",
      "X    39\n",
      "V    38\n",
      "A    38\n",
      "G    37\n",
      "P    35\n",
      "Q    35\n",
      "H    34\n",
      "B    34\n",
      "R    34\n",
      "L    34\n",
      "I    33\n",
      "Y    33\n",
      "O    32\n",
      "U    30\n",
      "N    29\n",
      "T    28\n",
      "K    27\n",
      "F    26\n",
      "S    26\n",
      "C    26\n",
      "W    25\n",
      "Z    22\n",
      "6    21\n",
      "4    19\n",
      "8    18\n",
      "2    16\n",
      "7    14\n",
      "0    14\n",
      "5    13\n",
      "3    13\n",
      "1    12\n",
      "9     5\n",
      "Name: key, dtype: int64\n",
      "8000    7\n",
      "8001    W\n",
      "8002    C\n",
      "8003    S\n",
      "8004    H\n",
      "8005    C\n",
      "8006    V\n",
      "8007    G\n",
      "8008    L\n",
      "8009    Q\n",
      "8010    Y\n",
      "8011    Y\n",
      "8012    9\n",
      "8013    K\n",
      "8014    N\n",
      "8015    4\n",
      "8016    P\n",
      "8017    Y\n",
      "8018    O\n",
      "8019    T\n",
      "8020    Y\n",
      "8021    E\n",
      "8022    4\n",
      "8023    9\n",
      "8024    U\n",
      "8025    H\n",
      "8026    C\n",
      "8027    E\n",
      "8028    7\n",
      "8029    9\n",
      "       ..\n",
      "8970    A\n",
      "8971    T\n",
      "8972    5\n",
      "8973    A\n",
      "8974    D\n",
      "8975    J\n",
      "8976    6\n",
      "8977    R\n",
      "8978    O\n",
      "8979    S\n",
      "8980    P\n",
      "8981    V\n",
      "8982    Z\n",
      "8983    O\n",
      "8984    3\n",
      "8985    F\n",
      "8986    R\n",
      "8987    Q\n",
      "8988    P\n",
      "8989    T\n",
      "8990    A\n",
      "8991    Q\n",
      "8992    1\n",
      "8993    W\n",
      "8994    S\n",
      "8995    W\n",
      "8996    N\n",
      "8997    Q\n",
      "8998    R\n",
      "8999    M\n",
      "Name: key, dtype: object\n",
      "Begine count\n",
      "O    42\n",
      "U    41\n",
      "S    40\n",
      "A    39\n",
      "V    37\n",
      "E    35\n",
      "T    35\n",
      "P    35\n",
      "M    34\n",
      "K    34\n",
      "Q    34\n",
      "J    34\n",
      "Z    33\n",
      "I    33\n",
      "Y    32\n",
      "W    32\n",
      "B    32\n",
      "R    29\n",
      "F    29\n",
      "D    28\n",
      "X    27\n",
      "N    27\n",
      "C    26\n",
      "H    25\n",
      "L    24\n",
      "G    24\n",
      "7    22\n",
      "4    21\n",
      "9    18\n",
      "2    17\n",
      "1    17\n",
      "3    15\n",
      "0    14\n",
      "8    12\n",
      "6    12\n",
      "5    11\n",
      "Name: key, dtype: int64\n",
      "9000    B\n",
      "9001    M\n",
      "9002    N\n",
      "9003    N\n",
      "9004    Y\n",
      "9005    0\n",
      "9006    R\n",
      "9007    3\n",
      "9008    C\n",
      "9009    D\n",
      "9010    U\n",
      "9011    3\n",
      "9012    M\n",
      "9013    6\n",
      "9014    N\n",
      "9015    L\n",
      "9016    I\n",
      "9017    E\n",
      "9018    C\n",
      "9019    X\n",
      "9020    B\n",
      "9021    D\n",
      "9022    K\n",
      "9023    L\n",
      "9024    Q\n",
      "9025    L\n",
      "9026    C\n",
      "9027    M\n",
      "9028    5\n",
      "9029    9\n",
      "       ..\n",
      "9970    4\n",
      "9971    1\n",
      "9972    Z\n",
      "9973    P\n",
      "9974    S\n",
      "9975    K\n",
      "9976    K\n",
      "9977    H\n",
      "9978    A\n",
      "9979    F\n",
      "9980    M\n",
      "9981    H\n",
      "9982    6\n",
      "9983    N\n",
      "9984    I\n",
      "9985    D\n",
      "9986    E\n",
      "9987    2\n",
      "9988    X\n",
      "9989    Q\n",
      "9990    8\n",
      "9991    W\n",
      "9992    A\n",
      "9993    X\n",
      "9994    H\n",
      "9995    L\n",
      "9996    E\n",
      "9997    K\n",
      "9998    G\n",
      "9999    0\n",
      "Name: key, dtype: object\n",
      "Begine count\n",
      "K    42\n",
      "M    41\n",
      "U    39\n",
      "Y    38\n",
      "P    38\n",
      "E    35\n",
      "R    34\n",
      "Q    34\n",
      "I    33\n",
      "B    33\n",
      "T    33\n",
      "F    33\n",
      "H    33\n",
      "A    32\n",
      "G    31\n",
      "Z    31\n",
      "L    30\n",
      "J    29\n",
      "N    29\n",
      "S    29\n",
      "X    28\n",
      "W    28\n",
      "C    28\n",
      "3    26\n",
      "D    25\n",
      "V    23\n",
      "6    22\n",
      "4    21\n",
      "O    19\n",
      "0    17\n",
      "2    15\n",
      "8    15\n",
      "5    15\n",
      "9    15\n",
      "7    14\n",
      "1    12\n",
      "Name: key, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "from pandas import Series\n",
    "tot = Series([])\n",
    "for pieces in df:\n",
    "    print pieces['key']\n",
    "    print 'Begine count'\n",
    "    print pieces['key'].value_counts()\n",
    "    tot = tot.add(pieces['key'].value_counts(),fill_value=0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    146.0\n",
       "9    150.0\n",
       "0    151.0\n",
       "2    152.0\n",
       "5    157.0\n",
       "3    162.0\n",
       "8    162.0\n",
       "7    164.0\n",
       "6    166.0\n",
       "4    171.0\n",
       "C    286.0\n",
       "Z    288.0\n",
       "B    302.0\n",
       "T    304.0\n",
       "W    305.0\n",
       "N    306.0\n",
       "S    308.0\n",
       "G    308.0\n",
       "Y    314.0\n",
       "R    318.0\n",
       "A    320.0\n",
       "D    320.0\n",
       "P    324.0\n",
       "U    326.0\n",
       "I    327.0\n",
       "V    328.0\n",
       "H    330.0\n",
       "K    334.0\n",
       "F    335.0\n",
       "J    337.0\n",
       "M    338.0\n",
       "Q    340.0\n",
       "O    343.0\n",
       "L    346.0\n",
       "X    364.0\n",
       "E    368.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tot.sort_values(ascending=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex5.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|something|a|b|c|d|message\n",
      "0|one|1|2|3.0|4|\n",
      "1|two|5|6||8|world\n",
      "2|three|9|10|11.0|12|foo\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "df.to_csv(sys.stdout,sep='|')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|something|a|b|c|d|message\n",
      "0|one|1|2|3.0|4|ULL\n",
      "1|two|5|6|ULL|8|world\n",
      "2|three|9|10|11.0|12|foo\n"
     ]
    }
   ],
   "source": [
    "df.to_csv(sys.stdout,sep='|',na_rep='ULL')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1|3.0\n",
      "5|FUCK\n",
      "9|11.0\n"
     ]
    }
   ],
   "source": [
    "df.to_csv(sys.stdout,sep='|',index=False,header=False,columns=['a','c'],na_rep='FUCK')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DatetimeIndex(['2000-01-01', '2000-01-02', '2000-01-03', '2000-01-04',\n",
      "               '2000-01-05', '2000-01-06', '2000-01-07'],\n",
      "              dtype='datetime64[ns]', freq='D')\n",
      "2000-01-01    0\n",
      "2000-01-02    1\n",
      "2000-01-03    2\n",
      "2000-01-04    3\n",
      "2000-01-05    4\n",
      "2000-01-06    5\n",
      "2000-01-07    6\n",
      "Freq: D, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "dates = pd.date_range('1/1/2000',periods=7)\n",
    "ts = Series(np.arange(7),index=dates)\n",
    "print dates\n",
    "print ts\n",
    "ts.to_csv('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/tseries.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2000-01-01,0\r\n",
      "2000-01-02,1\r\n",
      "2000-01-03,2\r\n",
      "2000-01-04,3\r\n",
      "2000-01-05,4\r\n",
      "2000-01-06,5\r\n",
      "2000-01-07,6\r\n"
     ]
    }
   ],
   "source": [
    "!cat /Users/linqiliang/Documents/PySci/data/pydata-book/ch06/tseries.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000-01-01    0\n",
       "2000-01-02    1\n",
       "2000-01-03    2\n",
       "2000-01-04    3\n",
       "2000-01-05    4\n",
       "2000-01-06    5\n",
       "2000-01-07    6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Series.from_csv('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/tseries.csv',parse_dates=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import csv\n",
    "f=open('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex7.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "reader=csv.reader(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['a', 'b', 'c']\n",
      "['1', '2', '3']\n",
      "['1', '2', '3', '4']\n"
     ]
    }
   ],
   "source": [
    "for line in reader:\n",
    "    print line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "lines= list(csv.reader(open('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex7.csv')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "header,values = lines[0],lines[1:]\n",
    "data_dict ={h: v for h,v in zip(header,zip(*values))}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'a': ('1', '1'), 'b': ('2', '2'), 'c': ('3', '3')}"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# data_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "class my_dialect(csv.Dialect):\n",
    "    lineterminator = '\\n'\n",
    "    delimiter = ';'\n",
    "    quotechar = '\"'\n",
    "    quoting= csv.QUOTE_ALL\n",
    "f=open('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/ex7.csv')   \n",
    "reader = csv.reader(f,dialect = my_dialect)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on reader object:\n",
      "\n",
      "class reader(__builtin__.object)\n",
      " |  CSV reader\n",
      " |  \n",
      " |  Reader objects are responsible for reading and parsing tabular data\n",
      " |  in CSV format.\n",
      " |  \n",
      " |  Methods defined here:\n",
      " |  \n",
      " |  __iter__(...)\n",
      " |      x.__iter__() <==> iter(x)\n",
      " |  \n",
      " |  next(...)\n",
      " |      x.next() -> the next value, or raise StopIteration\n",
      " |  \n",
      " |  ----------------------------------------------------------------------\n",
      " |  Data descriptors defined here:\n",
      " |  \n",
      " |  dialect\n",
      " |  \n",
      " |  line_num\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(reader)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['a,\"b\",\"c\"']\n",
      "['1,\"2\",\"3\"']\n",
      "['1,\"2\",\"3\",\"4\"']\n"
     ]
    }
   ],
   "source": [
    "for line in reader:\n",
    "    print line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3L"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reader.line_num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with open('mydata.csv','w') as f:\n",
    "    writer = csv.writer(f,dialect=my_dialect)\n",
    "    writer.writerow(('one','two','three'))\n",
    "    writer.writerow(('1','2','3'))\n",
    "    writer.writerow(('1','2','3'))\n",
    "    writer.writerow(('1','2','3'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "reader= csv.reader(open('mydata.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['one;\"two\";\"three\"']\n",
      "['1;\"2\";\"3\"']\n",
      "['1;\"2\";\"3\"']\n",
      "['1;\"2\";\"3\"']\n"
     ]
    }
   ],
   "source": [
    "for line in reader:\n",
    "    print line"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Json "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "obj = \"\"\"\n",
    "{\"name\": \"Wes\",\n",
    " \"places_lived\": [\"United States\", \"Spain\", \"Germany\"],\n",
    " \"pet\": null,\n",
    " \"siblings\": [{\"name\": \"Scott\", \"age\": 25, \"pet\": \"Zuko\"},\n",
    "              {\"name\": \"Katie\", \"age\": 33, \"pet\": \"Cisco\"}]\n",
    "}\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'name': u'Wes',\n",
       " u'pet': None,\n",
       " u'places_lived': [u'United States', u'Spain', u'Germany'],\n",
       " u'siblings': [{u'age': 25, u'name': u'Scott', u'pet': u'Zuko'},\n",
       "  {u'age': 33, u'name': u'Katie', u'pet': u'Cisco'}]}"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "result = json.loads(obj)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'Wes'"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result['name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "asjson = json.dumps(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from pandas import DataFrame\n",
    "siblings = DataFrame(result['siblings'],columns=['name','age'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Scott</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Katie</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age\n",
       "0  Scott   25\n",
       "1  Katie   33"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "siblings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Scott\n",
       "1    Katie\n",
       "Name: name, dtype: object"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "siblings['name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name    Scott\n",
       "age        25\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "siblings.ix[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name    Scott\n",
       "age        25\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "siblings.ix[0,['name','age']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "IOError",
     "evalue": "[Errno 2] No such file or directory: '/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/data.xls'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIOError\u001b[0m                                   Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-149-e67d77c52e8f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mpandas\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mxls_file\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mExcelFile\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/data.xls'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/usr/local/lib/python2.7/site-packages/pandas/io/excel.pyc\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, io, **kwds)\u001b[0m\n\u001b[1;32m    247\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbook\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mxlrd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mopen_workbook\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfile_contents\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    248\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mio\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcompat\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstring_types\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 249\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbook\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mxlrd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mopen_workbook\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mio\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    250\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    251\u001b[0m             raise ValueError('Must explicitly set engine if not passing in'\n",
      "\u001b[0;32m/usr/local/lib/python2.7/site-packages/xlrd/__init__.pyc\u001b[0m in \u001b[0;36mopen_workbook\u001b[0;34m(filename, logfile, verbosity, use_mmap, file_contents, encoding_override, formatting_info, on_demand, ragged_rows)\u001b[0m\n\u001b[1;32m    393\u001b[0m         \u001b[0mpeek\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfile_contents\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0mpeeksz\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    394\u001b[0m     \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 395\u001b[0;31m         \u001b[0;32mwith\u001b[0m \u001b[0mopen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfilename\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"rb\"\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    396\u001b[0m             \u001b[0mpeek\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpeeksz\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    397\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mpeek\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34mb\"PK\\x03\\x04\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;31m# a ZIP file\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mIOError\u001b[0m: [Errno 2] No such file or directory: '/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/data.xls'"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "xls_file = pd.ExcelFile('/Users/linqiliang/Documents/PySci/data/pydata-book/ch06/data.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "ImportError",
     "evalue": "No module named lxml.html",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mImportError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-151-741002527845>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0;32mimport\u001b[0m \u001b[0mlxml\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhtml\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mweb\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mImportError\u001b[0m: No module named lxml.html"
     ]
    }
   ],
   "source": [
    "import lxml.html as web"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
